﻿using Aspose.Cells;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace AsposeHelper
{
    public class CellsHelper
    {
        /// <summary>
        /// 将Excel中数据导出DataTable
        /// </summary>
        /// <param name="excelPath">Excel路径</param>
        /// <param name="sheetIndex">要导出的工作表索引</param>
        /// <returns></returns>
        public static DataTable ExportDataTable(string excelPath, int sheetIndex)
        {
            Workbook workbook = new Workbook(excelPath);
            if (workbook.Worksheets.Count < (sheetIndex + 1))
            {
                throw new Exception("Excel中没有第" + (sheetIndex + 1).ToString() + "个工作表！");
            }
            Cells cells = workbook.Worksheets[sheetIndex].Cells;
            if (cells.Count == 0)
            {
                throw new Exception("Excel选定的工作表中没有内容！");
            }

            //cells.MaxDataRow 包含数据的单元格最大行索引
            //cells.MaxDataColumn 包含数据的单元格最大列索引
            DataTable dt = cells.ExportDataTable(0, 0, cells.MaxDataRow + 1, cells.MaxDataColumn + 1, true);
            return dt;
        }

        /// <summary>
        /// 将DataSet导出Excel
        /// </summary>
        /// <param name="data">要导出的data(key为sheetName，value为要导出的数据)</param>
        /// <param name="savePath">保存的路径</param>
        /// <param name="maxQtySheet">单个Excel最大的工作表数量</param>
        /// <param name="fileName">文件名称</param>
        /// <returns>返回Excel保存的完整路径</returns>
        public static List<string> ExportExcel(Dictionary<string, DataTable> data, string savePath, int maxQtySheet, string fileName = "")
        {
            List<string> pathList = new List<string>();
            if (data == null || data.Count == 0)
            {
                throw new Exception("要导出的数据为空！");
            }
            else if (string.IsNullOrEmpty(savePath))
            {
                throw new Exception("保存位置不能为空！");
            }

            //新建工作簿  
            Workbook wb = null;
            List<string> keys = new List<string>(data.Keys);
            for (int i = 0; i < keys.Count; i++)
            {
                if (i % maxQtySheet == 0)
                {
                    wb = new Workbook();
                }
                DataTable dt = data[keys[i]];

                int sheetIndex = i % maxQtySheet;
                if (wb.Worksheets.Count <= sheetIndex)
                {
                    //新建的工作簿只有一个工作表，所以要添加工作表                    
                    wb.Worksheets.Add();
                }
                Worksheet ws = wb.Worksheets[sheetIndex];

                Cells cells = ws.Cells;
                int beginRowIndex = 0;
                if (!string.IsNullOrEmpty(dt.TableName))
                {
                    //工作表的名称最大长度为31
                    ws.Name = (keys[i].Substring(0, keys[i].Length > 31 ? 31 : keys[i].Length));

                    beginRowIndex = 1;

                    //新建样式  
                    Style titleStyle = wb.CreateStyle();
                    titleStyle.Font.IsBold = true;
                    titleStyle.Font.Size = 16;
                    titleStyle.VerticalAlignment = TextAlignmentType.Center;
                    titleStyle.HorizontalAlignment = TextAlignmentType.Center;
                    titleStyle.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
                    titleStyle.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
                    titleStyle.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
                    titleStyle.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;

                    cells.Merge(0, 0, 1, dt.Columns.Count);
                    cells[0, 0].PutValue(dt.TableName);
                    cells[0, 0].SetStyle(titleStyle);
                    cells.SetRowHeightPixel(0, 50);
                }

                Style colTitleStyle = wb.CreateStyle();
                colTitleStyle.Font.IsBold = true;
                colTitleStyle.VerticalAlignment = TextAlignmentType.Center;
                colTitleStyle.HorizontalAlignment = TextAlignmentType.Center;
                colTitleStyle.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
                colTitleStyle.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
                colTitleStyle.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
                colTitleStyle.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    cells[beginRowIndex, j].PutValue(dt.Columns[j].ColumnName);
                    cells[beginRowIndex, j].SetStyle(colTitleStyle);
                    cells.SetRowHeightPixel(beginRowIndex, 30);
                }

                Style colContextStyle = wb.CreateStyle();
                colContextStyle.Font.Size = 9;
                colContextStyle.VerticalAlignment = TextAlignmentType.Center;
                colContextStyle.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
                colContextStyle.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
                colContextStyle.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
                colContextStyle.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
                for (int j = 0; j < dt.Rows.Count; j++)
                {
                    for (int m = 0; m < dt.Columns.Count; m++)
                    {
                        if (dt.Rows[j][m].ToString() != "" && dt.Columns[m].DataType == typeof(decimal))
                        {
                            cells[beginRowIndex + 1 + j, m].PutValue(Convert.ToDecimal(dt.Rows[j][m].ToString()));
                            colContextStyle.HorizontalAlignment = TextAlignmentType.Right;
                        }
                        else if (dt.Rows[j][m].ToString() != "" && dt.Columns[m].DataType == typeof(int))
                        {
                            cells[beginRowIndex + 1 + j, m].PutValue(Convert.ToInt32(dt.Rows[j][m].ToString()));
                            colContextStyle.HorizontalAlignment = TextAlignmentType.Right;
                        }
                        else
                        {
                            colContextStyle.HorizontalAlignment = TextAlignmentType.Left;
                            cells[beginRowIndex + 1 + j, m].PutValue(dt.Rows[j][m].ToString());
                        }

                        if (dt.Columns[m].DataType == typeof(bool))
                        {
                            colContextStyle.HorizontalAlignment = TextAlignmentType.Center;
                        }

                        cells[beginRowIndex + 1 + j, m].SetStyle(colContextStyle);
                        cells.SetRowHeightPixel(beginRowIndex + 1 + j, 25);
                    }
                }

                if (maxQtySheet == 1
                    || i == keys.Count - 1
                    || (i != 0 && (i + 1) % maxQtySheet == 0))
                {
                    if (fileName.Length == 0)
                    {
                        fileName = "QD数据字典" + DateTime.Now.ToString("HHmmssfff");
                    }
                    string fullFieldName = Path.Combine(savePath, fileName + ".xlsx");
                    if (File.Exists(fullFieldName))
                    {
                        File.Delete(fullFieldName);
                    }
                    wb.Save(fullFieldName);
                    pathList.Add(fullFieldName);
                }
            }

            return pathList;
        }
    }
}
